#!/usr/bin/python
# coding=utf-8

import sqlite3
import pandas as pd
import sqlalchemy
from flask import Flask, render_template, jsonify, request, send_file
import numpy as np
import json
import jieba
import xgboost as xgb
import os
from flask_sqlalchemy import SQLAlchemy
from flask_admin import Admin
from flask_admin.contrib.sqla import ModelView
import seaborn as sns
import matplotlib.pyplot as plt
import io



app = Flask(__name__)
app.secret_key = os.urandom(24)
# app.secret_key = 'your_unique_secret_key_here'
app.config.from_object('config')


# 配置sqlite数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///job_info.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


DATABASE = 'job_info.db'


# 初始化Flask Admin
admin = Admin(app, name='后台管理')


# 定义模型
class Job(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    job_name = db.Column(db.String(255))
    hangye = db.Column(db.String(255))
    company = db.Column(db.String(255))
    province = db.Column(db.String(255))
    city = db.Column(db.String(255))
    salary = db.Column(db.String(255))
    jingyan = db.Column(db.String(255))
    xueli = db.Column(db.String(255))
    zhaopin_counts = db.Column(db.String(255))
    pub_time = db.Column(db.String(255))




# 自定义模型视图
class MyModelView(ModelView):
    # 设置字段显示名称
    column_labels = {
        'job_name': '职位名称',
        'hangye': '行业',
        'company': '公司',
        'province': '省份',
        'city': '城市',
        'salary': '薪资',
        'jingyan': '经验',
        'xueli': '学历',
        'zhaopin_counts': '招聘人数',
        'pub_time': '发布时间'
    }

    # 设置列显示顺序
    column_list = ('job_name', 'hangye', 'company', 'province', 'city', 'salary', 'jingyan', 'xueli', 'zhaopin_counts', 'pub_time')

    # 设置可搜索字段
    column_searchable_list = ('job_name',)

    # 设置分页大小
    page_size = 10

    def on_model_change(self, form, model, is_created):
        try:
            # 确保更新操作使用主键作为过滤条件
            db.session.query(Job).filter_by(id=model.id).update({
                Job.hangye: model.hangye,
                Job.company: model.company,
                Job.province: model.province,
                Job.city: model.city,
                Job.salary: model.salary,
                Job.jingyan: model.jingyan,
                Job.xueli: model.xueli,
                Job.zhaopin_counts: model.zhaopin_counts,
                Job.pub_time: model.pub_time
            })
            db.session.commit()
        except sqlalchemy.orm.exc.StaleDataError:
            db.session.rollback()
            # 根据实际需求处理异常，例如重新加载数据或记录日志
            print("StaleDataError occurred. Please check the data consistency.")


# 将模型添加到后台管理
admin.add_view(MyModelView(Job, db.session, name='数据管理'))



# --------------------- html render ---------------------
login_name = None


@app.route('/')
def root():
    return render_template('login.html')


@app.route('/login_page')
def login_page():
    return render_template('login.html')


@app.route('/register_page')
def register_page():
    return render_template('register.html')


@app.route('/index.html')
def index():
    return render_template('index.html')


@app.route('/show_data')
def show_data():
    return render_template('show_data.html')


@app.route('/hangye_analysis')
def hangye_analysis():
    return render_template('hangye_analysis.html')


@app.route('/yingpin_yaoqiu_analysis')
def yingpin_yaoqiu_analysis():
    return render_template('yingpin_yaoqiu_analysis.html')


@app.route('/job_wordcloud')
def job_wordcloud():
    return render_template('job_wordcloud.html')


@app.route('/job_recommend')
def job_recommend():
    return render_template('job_recommend.html')


@app.route('/salary_predict')
def salary_predict():
    return render_template('salary_predict.html')


# ------------------ ajax restful api -------------------
@app.route('/check_login')
def check_login():
    """判断用户是否登录"""
    return jsonify({'username': login_name, 'login': login_name is not None})


@app.route('/register/<name>/<password>')
def register(name, password):
    conn = sqlite3.connect('user_info.db')
    # 创建数据库游标，用于执行SQL命令
    cursor = conn.cursor()

    check_sql = "SELECT * FROM sqlite_master where type='table' and name='user'"
    # 执行SQL语句
    cursor.execute(check_sql)
    # 接收返回结果
    results = cursor.fetchall()
    # 数据库表不存在
    if len(results) == 0:
        # 创建数据库表
        sql = """
                CREATE TABLE user(
                    name CHAR(256), 
                    password CHAR(256)
                );
                """
        cursor.execute(sql)
        # 提交数据库操作
        conn.commit()
        print('创建数据库表成功！')

    sql = "INSERT INTO user (name, password) VALUES (?,?);"
    cursor.executemany(sql, [(name, password)])
    conn.commit()
    return jsonify({'info': '用户注册成功！', 'status': 'ok'})


@app.route('/login/<name>/<password>')
def login(name, password):
    global login_name
    conn = sqlite3.connect('user_info.db')
    cursor = conn.cursor()
    check_sql = "SELECT * FROM sqlite_master where type='table' and name='user'"
    cursor.execute(check_sql)
    results = cursor.fetchall()
    # 数据库表不存在
    if len(results) == 0:
        # 创建数据库表
        sql = """
                CREATE TABLE user(
                    name CHAR(256), 
                    password CHAR(256)
                );
                """
        cursor.execute(sql)
        conn.commit()
        print('创建数据库表成功！')

    sql = "select * from user where name='{}' and password='{}'".format(name, password)
    cursor.execute(sql)
    results = cursor.fetchall()

    login_name = name
    if len(results) > 0:
        print(results)
        return jsonify({'info': name + '用户登录成功！', 'status': 'ok'})
    else:
        return jsonify({'info': '当前用户不存在！', 'status': 'error'})


# 地理分区 与 省份的映射
dili_fengqu_shengfen_maps = {
    '华东': ['上海市', '江苏省', '浙江省', '安徽省', '江西省', '山东省', '福建省', '台湾省'],
    '华北': ['北京市', '天津市', '山西省', '河北省', '内蒙古自治区'],
    '华中': ['河南省', '湖北省', '湖南省'],
    '华南': ['广东省', '广西壮族自治区', '海南省', '香港特别行政区', '澳门特别行政区'],
    '西南': ['重庆市', '四川省', '贵州省', '云南省', '西藏自治区'],
    '西北': ['陕西省', '甘肃省', '青海省', '宁夏回族自治区', '新疆维吾尔自治区'],
    '东北': ['黑龙江省', '吉林省', '辽宁省']
}

# 省份与城市的映射
shengfen_city_dict = json.load(open('dili_fengqu.json', 'r', encoding='utf8'))
# 分区与城市的映射
dili_fengqu_cities_maps = {}

for fengqu in dili_fengqu_shengfen_maps:
    cities = []
    for shengfen in dili_fengqu_shengfen_maps[fengqu]:
        # 省份下的所有城市
        if shengfen in shengfen_city_dict:
            cities.extend(shengfen_city_dict[shengfen])

    dili_fengqu_cities_maps[fengqu] = set(cities)

# 城市 与 分区的映射
city_fenqu_maps = {}
for fengqu in dili_fengqu_shengfen_maps:
    for shengfen in dili_fengqu_shengfen_maps[fengqu]:
        if shengfen in shengfen_city_dict:
            # 省份下的所有城市
            for city in shengfen_city_dict[shengfen]:
                city_fenqu_maps[city] = fengqu

# 加载经纬度数据
districts = json.load(open('china_region.json', 'r', encoding='utf8'))['districts']

city_region_dict = {}
for province in districts:
    cities = province['districts']
    for city in cities:
        city_region_dict[city['name']] = {'longitude': city['center']['longitude'],
                                          'latitude': city['center']['latitude']}


# ------------------ ajax restful api -------------------

# 数据展示--查询页面

@app.route('/query_spidered_data')
#
# def query_spidered_data():
#     conn = sqlite3.connect(DATABASE)
#     cursor = conn.cursor()
#
#     check_sql = "SELECT * FROM job"
#     cursor.execute(check_sql)
#     jobs = cursor.fetchall()
#
#     hotjobs = []
#     for job in jobs:
#                 id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
#
#         try:
#             tmp = float(jingyan)
#             jingyan = '{}年工作经验'.format(jingyan)
#         except:
#             pass
#
#         hotjobs.append((job_name, hangye, company, province, salary, jingyan, xueli, zhaopin_counts, pub_time))
#
#     print('岗位总数：', len(hotjobs))
#     return jsonify(hotjobs[:20])




def query_spidered_data():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    # 获取前端传来的页码、每页显示条数和搜索关键词
    page = int(request.args.get('page', 1))  # 默认第一页
    per_page = int(request.args.get('per_page', 10))  # 默认每页显示10条
    search_keyword = request.args.get('search', '')  # 获取搜索关键词，默认为空

    # 计算查询的起始位置
    offset = (page - 1) * per_page

    # 查询总记录数
    if search_keyword:
        count_sql = "SELECT COUNT(*) FROM job WHERE job_name LIKE ?"
        cursor.execute(count_sql, ('%' + search_keyword + '%',))
    else:
        count_sql = "SELECT COUNT(*) FROM job"
        cursor.execute(count_sql)
    total_count = cursor.fetchone()[0]

    # 查询当前页的数据，并按照 pub_time 降序排序
    if search_keyword:
        query_sql = "SELECT * FROM job WHERE job_name LIKE ? ORDER BY pub_time DESC LIMIT ? OFFSET ?"
        cursor.execute(query_sql, ('%' + search_keyword + '%', per_page, offset))
    else:
        query_sql = "SELECT * FROM job ORDER BY pub_time DESC LIMIT ? OFFSET ?"
        cursor.execute(query_sql, (per_page, offset))
    jobs = cursor.fetchall()

    hotjobs = []
    for job in jobs:
        id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job

        #         id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job

        try:
            tmp = float(jingyan)
            jingyan = '{}年工作经验'.format(jingyan)
        except:
            pass

        hotjobs.append((job_name, hangye, company, province, salary, jingyan, xueli, zhaopin_counts, pub_time))

    # 返回数据和分页信息
    response_data = {
        'data': hotjobs,
        'total_count': total_count,
        'page': page,
        'per_page': per_page,
        'total_pages': (total_count + per_page - 1) // per_page
    }
    return jsonify(response_data)


@app.route('/job_hangye_analysis')
def job_hangye_analysis():
    """细分行业分析"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT hangye, salary FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    # 细分行业的个数
    hangye_counts = {}
    hangye_salary = {}
    for job in jobs:
        hangye, salary = job
        if hangye not in hangye_counts:
            hangye_counts[hangye] = 0
        hangye_counts[hangye] += 1

        # 假设 calc_salary 函数已定义
        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        if hangye not in hangye_salary:
            hangye_salary[hangye] = []
        hangye_salary[hangye].append(salary)

    hangye_counts = list(zip(list(hangye_counts.keys()), list(hangye_counts.values())))
    hangye_counts = sorted(hangye_counts, key=lambda k: k[1], reverse=True)

    # 过滤掉一些在招岗位很少的细分行业
    hangye_counts = [v for v in hangye_counts if v[1] > 10]
    hangye1 = [v[0] for v in hangye_counts][:40]
    counts = [v[1] for v in hangye_counts][:40]

    # 计算细分行业的平均薪资
    for hangye in hangye_salary:
        hangye_salary[hangye] = np.mean(hangye_salary[hangye])

    hangye_salary = list(zip(list(hangye_salary.keys()), list(hangye_salary.values())))
    hangye_salary = sorted(hangye_salary, key=lambda k: k[1], reverse=False)
    # 直接过滤掉 "农/林/牧/渔"
    hangye_salary = [v for v in hangye_salary if v[0] != "农/林/牧/渔"]
    hangye2 = [v[0] for v in hangye_salary][:40]
    salary = [v[1] for v in hangye_salary][:40]

    return jsonify({'细分行业': hangye1, '岗位数': counts, '细分行业2': hangye2, '平均薪资': salary})


@app.route('/dili_fengqu_analysis/<fengqu>')
def dili_fengqu_analysis(fengqu):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT hangye, city, salary FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    # 细分行业的个数
    hangye_counts = {}
    hangye_salary = {}
    for job in jobs:
        hangye, city, salary = job
        if '市' not in city:
            city = city + '市'
        if city not in city_fenqu_maps:
            continue
        if city_fenqu_maps[city] != fengqu:
            continue

        if hangye not in hangye_counts:
            hangye_counts[hangye] = 0
        hangye_counts[hangye] += 1

        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        if hangye not in hangye_salary:
            hangye_salary[hangye] = []
        hangye_salary[hangye].append(salary)

    hangye_counts = list(zip(list(hangye_counts.keys()), list(hangye_counts.values())))
    hangye_counts = sorted(hangye_counts, key=lambda k: k[1], reverse=True)

    # 过滤掉一些在招岗位很少的细分行业
    hangye1 = [v[0] for v in hangye_counts][:20]
    counts = [v[1] for v in hangye_counts][:20]

    # 计算细分行业的平均薪资
    for hangye in hangye_salary:
        hangye_salary[hangye] = np.mean(hangye_salary[hangye])

    hangye_salary = list(zip(list(hangye_salary.keys()), list(hangye_salary.values())))
    hangye_salary = sorted(hangye_salary, key=lambda k: k[1], reverse=False)
    hangye2 = [v[0] for v in hangye_salary][:20]
    salary = [v[1] for v in hangye_salary][:20]

    high_salary_hangyes = ' > '.join(hangye2[::-1][:3])
    return jsonify({'细分行业': hangye1, '岗位数': counts, '细分行业2': hangye2, '平均薪资': salary,
                    '高薪细分行业推荐': high_salary_hangyes})


@app.route('/fengqu_salary_analysis')
def fengqu_salary_analysis():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT hangye, city, salary FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    fengqu_high_salary = {'华东': [], '华北': [], '华中': [], '华南': [], '西南': [], '西北': [], '东北': []}
    fengqu_low_salary = {'华东': [], '华北': [], '华中': [], '华南': [], '西南': [], '西北': [], '东北': []}

    for job in jobs:
        hangye, city, salary = job
        if '市' not in city:
            city = city + '市'
        if city not in city_fenqu_maps:
            continue

        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        fengqu = city_fenqu_maps[city]
        fengqu_high_salary[fengqu].append(max_salary)
        fengqu_low_salary[fengqu].append(min_salary)

    fengqu = ['华东', '华北', '华中', '华南', '西南', '西北', '东北']
    high_salary = [np.mean(fengqu_high_salary[fq]) for fq in fengqu]
    low_salary = [np.mean(fengqu_low_salary[fq]) for fq in fengqu]

    return jsonify({'fengqu': fengqu, 'high_salary': high_salary, 'low_salary': low_salary})


def experience_to_numeric(exp):
    if '以上' in exp or '及以上' in exp:
        return int(exp.split('年')[0]) + 0.5  # 给"以上"加上0.5，代表这个值以上一点
    if '无需经验' in exp:
        return 0  # 无需经验为0
    if '-' in exp:
        start, end = exp.split('-')
        start = int(start.rstrip('年'))
        end = int(end.split('年')[0])
        return (start + end) / 2  # 返回区间的平均值
    if '年' in exp:
        return int(exp.split('年')[0])  # 直接是几年的情况
    return 0  # 兜底，其他情况返回0



@app.route('/query_yingpin_yaoqiu')
def query_yingpin_yaoqiu():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT jingyan, xueli, salary FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    jingyan_salary = {}
    xueli_salary = {}

    for job in jobs:
        jingyan, xueli, salary = job
        # print(jingyan)
        # try:
        #     jingyan = int(jingyan)
        #     jingyan = '{}年经验'.format(str(jingyan))
        # except:
        #     pass

        salary, min_salary, max_salary = calc_salary(salary)
        if salary:
            if jingyan not in jingyan_salary:
                jingyan_salary[jingyan] = []
            if xueli not in xueli_salary:
                xueli_salary[xueli] = []

            jingyan_salary[jingyan].append(salary)
            xueli_salary[xueli].append(salary)

    jingyan_job_counts = {}
    for jingyan in jingyan_salary:
        jingyan_job_counts[jingyan] = len(jingyan_salary[jingyan])
        jingyan_salary[jingyan] = np.mean(jingyan_salary[jingyan])

    jingyan_salary = list(zip(list(jingyan_salary.keys()), list(jingyan_salary.values())))
    jingyan_salary = sorted(jingyan_salary, key=lambda k: k[1], reverse=True)
    jingyan = [v[0] for v in jingyan_salary]
    jingyan_salary = [v[1] for v in jingyan_salary]
    jingyan_job_counts = [jingyan_job_counts[jy] for jy in jingyan]

    xueli_job_counts = {}
    for xueli in xueli_salary:
        xueli_job_counts[xueli] = len(xueli_salary[xueli])
        xueli_salary[xueli] = np.mean(xueli_salary[xueli] + [0])

    xueli_salary = list(zip(list(xueli_salary.keys()), list(xueli_salary.values())))
    xueli_salary = sorted(xueli_salary, key=lambda k: k[1], reverse=True)
    xueli = [v[0] for v in xueli_salary if '人' not in v[0]]
    xueli_salary = [v[1] for v in xueli_salary if '人' not in v[0]]
    xueli_job_counts = [xueli_job_counts[xl] for xl in xueli]

    sorted_experience = sorted(jingyan, key=experience_to_numeric, reverse=True)
    results = {'经验': sorted_experience, '经验平均薪资': jingyan_salary, '经验岗位数': jingyan_job_counts,
               '学历': xueli, '学历平均薪资': xueli_salary, '学历岗位数': xueli_job_counts}
    print(results)
    return jsonify(results)

'''
@app.route('/query_city_salary_region')
def query_city_salary_region():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT city, salary FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    city_salary = {}
    city_region = {}
    for job in jobs:
        city, salary = job
        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue
        if '市' not in city:
            city = city + '市'
        if city not in city_region_dict:
            continue

        if city not in city_salary:
            city_salary[city] = []

        city_salary[city].append(salary)
        loc = city_region_dict[city]
        city_region[city] = [loc['longitude'], loc['latitude']]

    city_mean_salary = []
    for city in city_salary:
        city_mean_salary.append({'name': city, 'value': np.mean(city_salary[city])})

    results = {'city_mean_salary': city_mean_salary, 'city_region': city_region}
    print(results)
    return jsonify(results)
'''

@app.route('/word_cloud/<fengqu>')
def word_cloud(fengqu):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT job_name, hangye, city FROM job"
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    wordclout_dict = {}
    for job in jobs:
        job_name, hangye, city = job
        if '市' not in city:
            city = city + '市'
        if city not in city_fenqu_maps:
            continue
        if city_fenqu_maps[city] != fengqu:
            continue

        # 对该地理分区下的职位进行分词
        text = job_name + hangye
        text = text.replace('（', '').replace('）', '').replace('/', '').replace('(', '').replace(')', '')
        words = jieba.cut(text)
        for word in words:
            word = word.strip()
            if word not in wordclout_dict:
                wordclout_dict[word] = 0
            else:
                wordclout_dict[word] += 1

    wordclout_dict = [(k, wordclout_dict[k]) for k in sorted(wordclout_dict.keys()) if wordclout_dict[k] > 2]
    wordclout_dict = [{"name": k[0], "value": k[1]} for k in wordclout_dict]

    return jsonify({'词云数据': wordclout_dict})


@app.route('/get_all_hangye')
def get_all_hangye():
    """获取所有细分行业"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    sql = 'select distinct hangye from job'
    cursor.execute(sql)
    hangyes = cursor.fetchall()
    hangyes = [h[0] for h in hangyes]
    print(hangyes)

    return jsonify(hangyes)


@app.route('/recommend_jobs')
def recommend_jobs():
    fengqu = request.args.get('fengqu')
    hangye = request.args.get('hangye')
    low_salary = float(request.args.get('low_salary'))
    high_salary = float(request.args.get('high_salary'))

    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT * FROM job where hangye='{}'".format(hangye)
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    hotjobs = []
    for job in jobs:
        id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        #         id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # 地区筛选
        if '市' not in city:
            city = city + '市'

        if city not in city_fenqu_maps:
            continue
        if city_fenqu_maps[city] != fengqu:
            continue

        try:
            tmp = float(jingyan)
            jingyan = '{}年工作经验'.format(jingyan)
        except:
            pass

        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        if (low_salary <= salary <= high_salary) or (low_salary >= salary >= high_salary):
            hotjobs.append((job_name, hangye, company, province, salary, jingyan, xueli, zhaopin_counts, pub_time))

    # 选取 top 20 的
    return jsonify(hotjobs[:20])


@app.route('/hangye_fengqu_salary')
def hangye_fengqu_salary():
    """不同地理分区的细分行业分析"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    hangye = request.args.get('hangye')
    print(hangye)
    check_sql = "SELECT hangye, city, salary FROM job where hangye=='{}'".format(hangye)
    cursor.execute(check_sql)
    jobs = cursor.fetchall()

    fengqu_salary = {}
    for job in jobs:
        hangye, city, salary = job
        if '市' not in city:
            city = city + '市'
        if city not in city_fenqu_maps:
            continue

        fengqu = city_fenqu_maps[city]

        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        if fengqu not in fengqu_salary:
            fengqu_salary[fengqu] = []
        fengqu_salary[fengqu].append(salary)

    # 计算平均薪资
    for fengqu in fengqu_salary:
        fengqu_salary[fengqu] = np.mean(fengqu_salary[fengqu])

    fengqu_salary = list(zip(list(fengqu_salary.keys()), list(fengqu_salary.values())))
    fengqu_salary = sorted(fengqu_salary, key=lambda k: k[1], reverse=False)
    fengqu = [v[0] for v in fengqu_salary]
    salary = [v[1] for v in fengqu_salary]
    max_salary = max(salary)
    return jsonify({'分区': fengqu, '平均薪资': salary, '最高薪资': max_salary})


def calc_salary(salary):
    """
    薪资数值化
    """
    salary = salary.split('·')[0]
    salary_data = salary.split('-')
    if len(salary_data) != 2:
        return None, None, None
    min_salary, max_salary = salary.split('-')[0], salary.split('-')[1]
    if '年' in salary:
        return None, None, None

    if max_salary.endswith('千'):
        max_scale = 1000
        max_salary = max_salary[:-1]
    elif salary.endswith('万'):
        max_scale = 10000
        max_salary = max_salary[:-1]
    else:
        return None, None, None

    if min_salary.endswith('千'):
        min_scale = 1000
        min_salary = min_salary[:-1]
    elif min_salary.endswith('万'):
        min_scale = 10000
        min_salary = min_salary[:-1]
    else:
        min_scale = max_scale
    # 计算平均薪资
    max_salary = max_scale * float(max_salary)
    min_salary = min_scale * float(min_salary)
    salary = min_salary + max_salary / 2
    return salary, min_salary, max_salary




# 薪资预测模型
sql = 'select hangye,xueli,city,jingyan,salary from job'
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
cursor.execute(sql)
jobs = cursor.fetchall()

train_datas = []
for job in jobs:
    hangye, xueli, city, jingyan, salary = job

    if city + '市' not in city_fenqu_maps:
        continue

    fengqu = city_fenqu_maps[city + '市']
    salary, min_salary, max_salary = calc_salary(salary)
    if salary:
        train_datas.append([hangye, xueli, fengqu, jingyan, salary])

train_datas = pd.DataFrame(train_datas, columns=['hangye', 'xueli', 'fengqu', 'jingyan', 'salary'])

# 细分行业
hangye_mean = train_datas.groupby('hangye')['salary'].mean().reset_index().sort_values(by='salary', ascending=False)
all_hangye = hangye_mean['hangye'].values.tolist()

# 学历
xueli_mean = train_datas.groupby('xueli')['salary'].mean().reset_index().sort_values(by='salary', ascending=False)
all_xueli = xueli_mean['xueli'].values.tolist()

# 地区
fengqu_mean = train_datas.groupby('fengqu')['salary'].mean().reset_index().sort_values(by='salary', ascending=False)
all_fengqu = fengqu_mean['fengqu'].values.tolist()

# 工作经验
jingyan_mean = train_datas.groupby('jingyan')['salary'].mean().reset_index().sort_values(by='salary', ascending=False)
all_jingyan = jingyan_mean['jingyan'].values.tolist()

hangye_map = {hy: i for i, hy in enumerate(all_hangye)}
xueli_map = {hy: i for i, hy in enumerate(all_xueli)}
fengqu_map = {hy: i for i, hy in enumerate(all_fengqu)}
jingyan_map = {hy: i for i, hy in enumerate(all_jingyan)}

print(xueli_map)
print(hangye_map)
print(fengqu_map)
print(jingyan_map)

# 1、构造决策树机器学习模型
train_datas['hangye'] = train_datas['hangye'].map(hangye_map)
train_datas['xueli'] = train_datas['xueli'].map(xueli_map)
train_datas['fengqu'] = train_datas['fengqu'].map(fengqu_map)
train_datas['jingyan'] = train_datas['jingyan'].map(jingyan_map)
# 导入机器学习库
from sklearn.tree import DecisionTreeRegressor
from sklearn.metrics import mean_absolute_error, r2_score
from sklearn.model_selection import train_test_split

y = train_datas['salary'].values
del train_datas['salary']


def train_regr():
    # 创建回归模型
    regr = DecisionTreeRegressor(max_depth=19)
    # 目标变量
    X = train_datas.values
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
    regr.fit(X, y)
    pred = regr.predict(X)
    mae = mean_absolute_error(y, pred)
    r2 = r2_score(y, pred)
    # 新增：输出决策树模型的MSE和R方
    from sklearn.metrics import mean_squared_error
    mse = mean_squared_error(y, pred)
    print(f'决策树模型训练集MSE: {mse:.2f}，R方: {r2:.4f}')
    return regr


# 2、XGBOOST回归模型
def train_xgb():
    # 构建目标变量
    X = train_datas.values
    # 划分训练集
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
    xgb_models = xgb.XGBRegressor(
        objective="reg:squarederror",
        colsample_bytree=0.5,
        subsample=0.8,
        learning_rate=0.01,
        max_depth=10,
        alpha=10,
        n_estimators=500,
        min_child_weight=3,
        gamma=2,
        random_state=42
    )
    xgb_models.fit(X_train, y_train)
    pred = xgb_models.predict(X_test)
    mae = mean_absolute_error(y_test, pred)
    r2 = r2_score(y_test, pred)
    # 新增：输出XGBoost模型的MSE和R方
    from sklearn.metrics import mean_squared_error
    mse = mean_squared_error(y_test, pred)
    print(f'XGBoost模型测试集MSE: {mse:.2f}，R方: {r2:.4f}')
    return xgb_models
regr = train_regr()
xgb_models = train_xgb()




@app.route('/get_selectors')
def get_selectors():
    return jsonify({
        'hangye': all_hangye,
        'xueli': all_xueli,
        'fengqu': all_fengqu,
        'jingyan': all_jingyan,
    })


@app.route('/tree_model_predict_salary')
def tree_model_predict_salary():
    """根据学历，工作经验，求职岗位等预测出对应的薪资情况"""
    fengqu = request.args.get('fengqu')
    hangye = request.args.get('hangye')
    xueli = request.args.get('xueli')
    jingyan = request.args.get('jingyan')

    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    check_sql = "SELECT * FROM job where hangye='{}' and xueli='{}'".format(hangye, xueli)
    cursor.execute(check_sql)
    jobs = cursor.fetchall()
    hotjobs = []
    job_set = set()
    for job in jobs:
        id,job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job
        # job_name, hangye, company, province, city, salary, jingyan, xueli, zhaopin_counts, pub_time = job

        # 地区筛选
        if '市' not in city:
            city = city + '市'
        if city not in city_fenqu_maps:
            continue
        if city_fenqu_maps[city] != fengqu:
            continue

        try:
            tmp = float(jingyan)
            jingyan = '{}年工作经验'.format(jingyan)
        except:
            pass

        # 薪资筛选
        salary, min_salary, max_salary = calc_salary(salary)
        if not salary:
            continue

        if f'{job_name}-{company}-{salary}-{pub_time}' not in job_set:
            hotjobs.append((job_name, hangye, company, province, salary, jingyan, xueli, zhaopin_counts, pub_time))
        job_set.add(f'{job_name}-{company}-{salary}-{pub_time}')

    # 选取 top 20 的
    top_salarys = hotjobs[:20]

    hangye, xueli, fengqu, jingyan = hangye_map[hangye], xueli_map[xueli], fengqu_map[fengqu], jingyan_map[jingyan]
    # 模型预测
    pred_salary = regr.predict(np.array([hangye, xueli, fengqu, jingyan]).reshape(1, -1))
    pred_salary = pred_salary[0]

    xgb_pred_salary = xgb_models.predict(np.array([hangye, xueli, fengqu, jingyan]).reshape(1, -1))
    xgb_pred_salary = xgb_pred_salary[0]
    return jsonify({'pred_salary': pred_salary, 'recommend_jobs': top_salarys, "xgb_pred_salary":str(xgb_pred_salary)})


@app.route('/correlation_heatmap')
def correlation_heatmap():
    """计算岗位数据各特征的皮尔逊相关系数并返回热力图图片（含更多字段）"""
    conn = sqlite3.connect(DATABASE)
    df = pd.read_sql_query("SELECT salary, jingyan, xueli, hangye, city, zhaopin_counts FROM job", conn)
    conn.close()

    # 薪资
    def parse_salary(s):
        try:
            avg, _, _ = calc_salary(s)
            return avg
        except:
            return None
    df['salary'] = df['salary'].apply(parse_salary)

    # 经验
    def exp_to_num(x):
        if pd.isnull(x):
            return None
        if '无需' in x or '应届' in x or '在校' in x:
            return 0
        import re
        nums = re.findall(r'\d+', str(x))
        if len(nums) == 1:
            return int(nums[0])
        elif len(nums) == 2:
            return (int(nums[0]) + int(nums[1])) / 2
        else:
            return None
    df['jingyan'] = df['jingyan'].apply(exp_to_num)

    # 学历
    xueli_map = {'博士': 4, '硕士': 3, '本科': 2, '大专': 1, '高中': 0, '中技/中专': 0, '': None}
    df['xueli'] = df['xueli'].map(xueli_map)

    # 行业编码
    hangye_list = df['hangye'].dropna().unique().tolist()
    hangye_map = {v: i for i, v in enumerate(hangye_list)}
    df['hangye'] = df['hangye'].map(hangye_map)

    # 地区编码（用city映射到分区）
    df['fengqu'] = df['city'].apply(lambda c: city_fenqu_maps.get(c+'市', None) if '市' not in str(c) else city_fenqu_maps.get(c, None))
    fengqu_list = df['fengqu'].dropna().unique().tolist()
    fengqu_map = {v: i for i, v in enumerate(fengqu_list)}
    df['fengqu'] = df['fengqu'].map(fengqu_map)

    # 招聘人数
    def zhaopin_to_num(x):
        try:
            return int(x)
        except:
            return None
    df['zhaopin_counts'] = df['zhaopin_counts'].apply(zhaopin_to_num)

    # 只保留数值型
    df = df[['salary', 'jingyan', 'xueli', 'hangye', 'fengqu', 'zhaopin_counts']].dropna()
    corr = df.corr(method='pearson')

    # 设置matplotlib支持中文
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False
    plt.figure(figsize=(7,6))
    sns.heatmap(corr, annot=True, cmap='coolwarm', fmt='.2f', vmin=-1, vmax=1)
    plt.title('岗位特征相关性热力图')
    buf = io.BytesIO()
    plt.tight_layout()
    plt.savefig(buf, format='png')
    plt.close()
    buf.seek(0)
    return send_file(buf, mimetype='image/png')


@app.route('/correlation_heatmap_data')
def correlation_heatmap_data():
    """返回岗位特征相关系数矩阵JSON数据（含更多字段）"""
    conn = sqlite3.connect(DATABASE)
    df = pd.read_sql_query("SELECT salary, jingyan, xueli, hangye, city, zhaopin_counts FROM job", conn)
    conn.close()

    def parse_salary(s):
        try:
            avg, _, _ = calc_salary(s)
            return avg
        except:
            return None
    df['salary'] = df['salary'].apply(parse_salary)

    def exp_to_num(x):
        if pd.isnull(x):
            return None
        if '无需' in x or '应届' in x or '在校' in x:
            return 0
        import re
        nums = re.findall(r'\d+', str(x))
        if len(nums) == 1:
            return int(nums[0])
        elif len(nums) == 2:
            return (int(nums[0]) + int(nums[1])) / 2
        else:
            return None
    df['jingyan'] = df['jingyan'].apply(exp_to_num)

    xueli_map = {'博士': 4, '硕士': 3, '本科': 2, '大专': 1, '高中': 0, '中技/中专': 0, '': None}
    df['xueli'] = df['xueli'].map(xueli_map)

    hangye_list = df['hangye'].dropna().unique().tolist()
    hangye_map = {v: i for i, v in enumerate(hangye_list)}
    df['hangye'] = df['hangye'].map(hangye_map)

    df['fengqu'] = df['city'].apply(lambda c: city_fenqu_maps.get(c+'市', None) if '市' not in str(c) else city_fenqu_maps.get(c, None))
    fengqu_list = df['fengqu'].dropna().unique().tolist()
    fengqu_map = {v: i for i, v in enumerate(fengqu_list)}
    df['fengqu'] = df['fengqu'].map(fengqu_map)

    def zhaopin_to_num(x):
        try:
            return int(x)
        except:
            return None
    df['zhaopin_counts'] = df['zhaopin_counts'].apply(zhaopin_to_num)

    df = df[['salary', 'jingyan', 'xueli', 'hangye', 'fengqu', 'zhaopin_counts']].dropna()
    corr = df.corr(method='pearson')
    labels = list(corr.columns)
    matrix = corr.values.tolist()
    return jsonify({'labels': labels, 'matrix': matrix})


@app.route('/salary_predict_curve')
def salary_predict_curve():
    """返回不同经验区间下决策树和XGBoost模型的预测薪资序列和真实薪资中位数"""
    fengqu = request.args.get('fengqu')
    hangye = request.args.get('hangye')
    xueli = request.args.get('xueli')
    exp_list = ['1年', '2年', '3年', '5年', '8年', '10年及以上']
    jingyan_map = globals().get('jingyan_map', {})
    dt_result = []
    xgb_result = []
    dt_true = []
    xgb_true = []

    # 真实值计算：从原始数据中筛选出对应条件和经验区间的真实薪资，取中位数
    conn = sqlite3.connect(DATABASE)
    df = pd.read_sql_query("SELECT hangye, xueli, city, jingyan, salary FROM job", conn)
    conn.close()
    # 经验区间映射
    def exp_to_group(x):
        if '10' in x:
            return '10年及以上'
        elif '8' in x:
            return '8年'
        elif '5' in x:
            return '5年'
        elif '3' in x:
            return '3年'
        elif '2' in x:
            return '2年'
        elif '1' in x:
            return '1年'
        else:
            return None
    df['exp_group'] = df['jingyan'].apply(lambda x: exp_to_group(str(x)))
    # 处理薪资
    def parse_salary(s):
        try:
            avg, _, _ = calc_salary(s)
            return avg
        except:
            return None
    df['salary_num'] = df['salary'].apply(parse_salary)
    # 处理地区
    df['fengqu'] = df['city'].apply(lambda c: city_fenqu_maps.get(c+'市', None) if '市' not in str(c) else city_fenqu_maps.get(c, None))
    # 只保留匹配条件的
    df = df[(df['hangye'] == hangye) & (df['xueli'] == xueli) & (df['fengqu'] == fengqu)]

    for exp in exp_list:
        # 预测
        if exp not in jingyan_map:
            dt_result.append(None)
            xgb_result.append(None)
            dt_true.append(None)
            xgb_true.append(None)
            continue
        try:
            h = hangye_map[hangye]
            x = xueli_map[xueli]
            f = fengqu_map[fengqu]
            j = jingyan_map[exp]
            X = np.array([h, x, f, j]).reshape(1, -1)
            dt_pred = regr.predict(X)[0]
            xgb_pred = xgb_models.predict(X)[0]
            dt_result.append(round(dt_pred/10000, 2))
            xgb_result.append(round(xgb_pred/10000, 2))
        except Exception as e:
            dt_result.append(None)
            xgb_result.append(None)
        # 真实值
        group_df = df[df['exp_group'] == exp]
        if not group_df.empty:
            median_salary = group_df['salary_num'].median()
            median_salary = round(median_salary/10000, 2) if median_salary else None
        else:
            median_salary = None
        dt_true.append(median_salary)
        xgb_true.append(median_salary)
    return jsonify({
        'exp_list': exp_list,
        'dt_result': dt_result,
        'xgb_result': xgb_result,
        'dt_true': dt_true,
        'xgb_true': xgb_true
    })


if __name__ == "__main__":
    app.run(host='127.0.0.1')
